package com.jysz.certificate.persistence.dao.cs;

import java.util.Date;
import java.util.List;
import java.util.Map;

import org.hibernate.Criteria;
import org.hibernate.SQLQuery;
import org.springframework.stereotype.Repository;

import com.jysz.certificate.persistence.dao.AbstractBaseDao;
import com.jysz.certificate.persistence.enums.PostType;
import com.jysz.certificate.persistence.model.cs.CheckStock;
import com.jysz.certificate.persistence.model.system.Financial;
import com.jysz.certificate.persistence.model.system.User;
import com.jysz.certificate.utils.CommonUtil;
import com.jysz.certificate.utils.DateUtil;

/**
 * 盘库DAO
 * @author xuxiaolong
 */
@Repository
public class CheckStockDao extends AbstractBaseDao<CheckStock, Long> {

	public List<Map<String, Object>> search(User currentUser,List<Financial> bankList,
			Date beginDate, Date endDate,int success) {
		
		StringBuilder sb = new StringBuilder(
				"SELECT csId,csDate,submitDate,opremark,shopId,live,source,");
		for (int i = 0; i < bankList.size(); i++) {
			Financial bank = bankList.get(i);
			sb.append(" SUM(CASE WHEN bankId=").append(bank.getId())
					.append(" THEN bankNum ELSE 0 END) AS '")
					.append(bank.getBrief()).append("' ");
			if (i != bankList.size() - 1)
				sb.append(" , ");
		}
		sb.append(" FROM (");
		sb.append("	SELECT op.id AS opid ,op._checkstock_id AS csId ,");
		sb.append("	DATE_FORMAT(cs._submit_date, '%Y-%m') AS csDate,DATE_FORMAT(cs._submit_date, '%Y/%m/%d') AS submitDate,cs._shop_id AS shopId,cs._remark AS opremark,cs._live AS live,cs._source AS source,");
		sb.append("	cm._financial_id AS bankId,COUNT(*) AS bankNum");
		sb.append("	FROM cs_checkstock_option_ op  ,cm_certification_  cm ,cs_checkstock_ cs ");
		sb.append("	WHERE  op._certification_id =cm.id AND op._checkstock_id=cs.id");
		sb.append("	 AND cs.id IN( SELECT MAX(cs.id)  FROM cs_checkstock_ cs ");
		sb.append(" WHERE cs._submit_date >=  STR_TO_DATE('")
				.append(DateUtil.formatDate(beginDate, "yyyy-MM-dd")).append(" 00:00:00")
				.append("','%Y-%m-%d %H:%i:%s')");
		sb.append(" AND cs._submit_date <=  STR_TO_DATE('")
				.append(DateUtil.formatDate(endDate, "yyyy-MM-dd")).append(" 23:59:59")
				.append("','%Y-%m-%d %H:%i:%s')");
		sb.append(" AND cs._status=1 ");
		if(success!=-1){
			sb.append(" AND cs._success =").append(success);
		}
		    
		//权限
		if(currentUser.getPost().compareTo(PostType.Admin)!=0){
			String shopIds=CommonUtil.getStringFromArray(super.getShops(currentUser));
			if(CommonUtil.isNotEmpty(shopIds)){
				sb.append(" AND cs._shop_id in (").append(shopIds).append(")");
			}else{
				sb.append(" AND 1!=1 ");
			}
		}
		
		sb.append("	 GROUP BY DATE_FORMAT(cs._submit_date, '%Y-%m'),cs._shop_id,cs._source");
		sb.append("	ORDER BY cs._submit_date DESC )");
		sb.append("	GROUP BY csID,bankID");
		sb.append(" ) AS rus");
		sb.append(" GROUP BY csId");
		sb.append(" ORDER BY csDate ASC");

		SQLQuery sqlQuery = getSession().createSQLQuery(sb.toString());
		sqlQuery.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
		return sqlQuery.list();
	}
	
	//查询未盘库的店铺
	public List<Map<String, Object>> search(User currentUser,Date beginDate, Date endDate) {
		
		StringBuilder sb = new StringBuilder(
				"SELECT shop._shop_mark shopNum, shop._shuo_name shopName,");
		sb.append(" (SELECT GROUP_CONCAT(organization._name) FROM sys_organization_ organization WHERE organization._parent_id = shop._area  AND organization._type = 2) areaManager, ");
		sb.append(" (SELECT GROUP_CONCAT(organization._name) FROM sys_organization_ organization ");
		sb.append(" WHERE _parent_id = (SELECT DISTINCT id FROM sys_organization_ WHERE _object_id = shop.id AND _type = 1) ");
		sb.append(" AND organization._type=2) shopUser ");
		sb.append(" FROM  sys_shop_ shop  WHERE   shop.id NOT IN ");
		sb.append(" ( SELECT cs._shop_id FROM cs_checkstock_ AS cs  ");
		sb.append("  WHERE  cs._submit_date >= STR_TO_DATE('");
		sb.append(DateUtil.formatDate(beginDate, "yyyy-MM-dd")).append(" 00:00:00")
		.append("','%Y-%m-%d %H:%i:%s')");
		sb.append(" AND cs._submit_date <=  STR_TO_DATE('")
		.append(DateUtil.formatDate(endDate, "yyyy-MM-dd")).append(" 23:59:59")
		.append("','%Y-%m-%d %H:%i:%s')");
		sb.append(" AND cs._status=1 ) ");
		
		//权限
		if(currentUser.getPost().compareTo(PostType.Admin)!=0){
			String shopIds=CommonUtil.getStringFromArray(super.getShops(currentUser));
			if(CommonUtil.isNotEmpty(shopIds)){
				sb.append(" AND shop.id in (").append(shopIds).append(")");
			}else{
				sb.append(" AND 1!=1 ");
			}
		}
		
		SQLQuery sqlQuery = getSession().createSQLQuery(sb.toString());
		sqlQuery.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
		return sqlQuery.list();
	}
}
